//
//  DataBase.m
//  Lesson_UI_17_Sqlite
//
//  Created by dlios on 15-8-25.
//  Copyright (c) 2015年 GF. All rights reserved.
//

#import "DataBase.h"

@implementation DataBase


+ (DataBase *)shareDataBase{
    static DataBase *dbHandle = nil;
    if (dbHandle == nil) {
        dbHandle = [[DataBase alloc] init];
    }
    return dbHandle;
}


static sqlite3 *db;//创建一个数据库对象

//打开数据库
- (void)openDB{
    
    //获取沙盒路径
    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    
    
    //拼接sqlite文件的名字
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"student.sqlite"];
    NSLog(@"路径 = %@", sqlitePath);
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    //打开数据库的方法
    int result = sqlite3_open(sqlitePath.UTF8String,&db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
        
    }else{
        NSLog(@"数据库打开失败");
    }
}


//创建数据库表(小写的可以改, 大写的不能改)
- (void)createTable{
    //生成创建表的sql语句
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS lanou0722(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, pic TEXT )";
    
    //执行sql语句
    int result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败"); 
    }
    
}


//插入信息(增)
- (void)insertStudent:(firstTableModel *)model{
    
    //创建插入学生信息的sql语句
    NSString *insertSql = [NSString stringWithFormat: @"INSERT INTO lanou0722(title, pic)VALUES( '%@', '%@')", model.title, model.pic];
    //执行sql语句
    int result  = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"success");
        
    }else{
        NSLog(@"failed");
    }
}


//更新信息(改)
//- (void)updateStudent:(StudentModel *)model number:(NSInteger)number{
//    NSString *updataSql = [NSString stringWithFormat:@"UPDATE lanou0722 SET name = '%@', sex = '%@', age= '%ld' WHERE number = '%ld'", model.name, model.sex,model.age, number];
//    int result = sqlite3_exec(db, updataSql.UTF8String, NULL, NULL, nil);
//    if (result == SQLITE_OK) {
//        NSLog(@"sucess");
//    }else{
//        NSLog(@"failed");
//    }
//    
//}

//根据number删除学生信息
- (void)deleteStudent: (NSString *)title{
    //创建删除学生信息SQL语句
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM lanou0722 WHERE title = '%@'", title];
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"sucess");
    }
    else{
        NSLog(@"failed");
    }
}


//查询信息(查)
- (NSMutableArray *)selectAll{
    NSString *selectSql = @"SELECT * FROM lanou0722";
    
    //创建数据库跟随指针,用来循环遍历数据库表的每行数据
    sqlite3_stmt *stmt = nil;
    
    //参数1: 数据库对象
    //参数2: 查询语句
    //参数3: 查询语句字数限制 (-1为不限制)
    //参数4: 跟随只针对象
    //参数5: nil
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放信息的大数组
    NSMutableArray *array = [[NSMutableArray alloc] init];
    
    
    if (result ==SQLITE_OK) {
        NSLog(@"查询准备工作完成");
    
    
    //开始循环遍历每一条数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //当跟随指针指向的行的数据符合我的查询条件, 逐一取出每列数据
        
        int number = sqlite3_column_int(stmt, 0);
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        const unsigned char *pic = sqlite3_column_text(stmt, 2);
        
        
        NSString *titleString = [NSString stringWithUTF8String:(const char *)title];
        NSString *picString = [NSString stringWithUTF8String:(const char *)pic];
        
        firstTableModel *model = [[firstTableModel alloc] init];
        model.title = titleString;
        model.pic = picString;
        
        
        //将学生对象存入大数组
        [array addObject:model];

    }
    }else{
        NSLog(@"failed");
    }
    return array;
   
}

- (void)closeDB{
    db = nil;
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
        db = nil;
    }else{
        NSLog(@"关闭数据库失败");
    }
}

- (void)dropTable{
    NSString *dropSql = @"DROP TABLE lanou0722";
    int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"success");
    }else{
        NSLog(@"failed");
    }
}

- (void)createCacheTable
{
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS cache(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, pic TEXT )";
    
    //执行sql语句
    int result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建缓存表成功");
    }else{
        NSLog(@"创建缓存表失败");
    }
}

- (void)updateCacheStudent:(firstTableModel *)model number:(NSInteger)number
{
    NSString *updataSql = [NSString stringWithFormat:@"UPDATE cache SET title = '%@', pic = '%@',  WHERE number = '%ld'", model.title, model.pic, number];
    int result = sqlite3_exec(db, updataSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新缓存表sucess");
    }else{
        NSLog(@"更新缓存表failed");
    }
}

- (void)insertCacheStudent:(firstTableModel *)model
{
    NSString *insertSql = [NSString stringWithFormat: @"INSERT INTO cache(title, pic)VALUES( '%@', '%@')", model.title, model.pic];
    //执行sql语句
    int result  = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入缓存success");
        
    }else{
        NSLog(@"插入缓存failed");
    }

}

- (void)deleteCacheStudent:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM cache WHERE number = '%ld'", number];
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除缓存sucess");
    }
    else{
        NSLog(@"删除缓存failed");
    }
}

- (NSMutableArray *)selectCacheAll
{
    NSString *selectSql = @"SELECT * FROM cache";
    
    //创建数据库跟随指针,用来循环遍历数据库表的每行数据
    sqlite3_stmt *stmt = nil;
    
    //参数1: 数据库对象
    //参数2: 查询语句
    //参数3: 查询语句字数限制 (-1为不限制)
    //参数4: 跟随只针对象
    //参数5: nil
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放信息的大数组
    NSMutableArray *array = [[NSMutableArray alloc] init];
    
    
    if (result ==SQLITE_OK) {
        NSLog(@"查询准备工作完成");
        
        
        //开始循环遍历每一条数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合我的查询条件, 逐一取出每列数据
            
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            
            
            NSString *titleString = [NSString stringWithUTF8String:(const char *)title];
            NSString *picString = [NSString stringWithUTF8String:(const char *)pic];
            
            firstTableModel *model = [[firstTableModel alloc] init];
            model.title = titleString;
            model.pic = picString;
            
            
            //将学生对象存入大数组
            [array addObject:model];
            
        }
    }else{
        NSLog(@"遍历缓存failed");
    }
    return array;

}

- (void)dropCacheTable
{
    NSString *dropSql = @"DROP TABLE cache";
    int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除缓存表success");
    }else{
        NSLog(@"删除缓存表failed");
    }

}

@end
